import datetime
from db_conn import pd_conn_zfw


def query_new_user_monthly(startTime, endTime):
    sql = """
        select t1.f_user_id
        from 
            (
                select distinct o.f_user_id from t_order_full o force index (order_date_index)
                where o.f_order_date >= '{_startTime}' and o.f_order_date < '{_endTime}' 
                and (o.f_status in (50, 60) OR ( o.f_status = 15 AND o.f_pay_type IN (
                    SELECT DISTINCT f_pay_type FROM t_order_income_config WHERE f_status = 1 AND f_is_refund = 0) ) )
                AND f_is_insider = 0 AND f_service_id = 2002 
            ) t1 
            left join (
                select distinct o.f_user_id from t_order_full o force index (order_date_index)
                where o.f_order_date < '{_startTime}' 
                and (o.f_status in (50, 60) OR ( o.f_status = 15 AND o.f_pay_type IN (
                    SELECT DISTINCT f_pay_type FROM t_order_income_config WHERE f_status = 1 AND f_is_refund = 0) ) )
                AND f_is_insider = 0 AND f_service_id = 2002 
            ) t2 on t1.f_user_id = t2.f_user_id 
        where t2.f_user_id is null;
    """.format(_startTime=startTime, _endTime=endTime)
    return pd_conn_zfw(sql)


def export_new_users(startTime, endTime):
    while startTime != endTime:
        reduceOneDay = (datetime.datetime.strptime(startTime, '%Y-%m-%d') + datetime.timedelta(days=32)).replace(
            day=1).strftime("%Y-%m-%d")
        df = query_new_user_monthly(startTime, reduceOneDay)
        df.to_csv(r'D:\Store\Python-Download\全国月新用户\全国月新用户_{}.csv'.format(startTime[:7]))
        startTime = reduceOneDay
        print(reduceOneDay)


if __name__ == '__main__':

    startTime = '2021-08-01'    # 每月变更
    endTime = '2022-01-01'      # 21年数据不变


